/**
 * Project: topdt-jbpm
 * Copyright 2013  Top Digital Technology  Co. Ltd.
 * All right reserved.
 */
package com.topdt.jbpm.dao;

import java.util.List;
import java.util.ResourceBundle;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.thx.common.orm.HibernateBaseDao;
import com.thx.common.util.AppContext;
import com.topdt.jbpm.model.WorkOpinion;

/**
 * 建立日期 : 2013-4-25 下午12:03:16<br>
 * 作者 : balance<br>
 * 模块 : <br>
 * 描述 : <br>
 * 修改历史: 序号 日期 修改人 修改原因 <br>
 * 1 <br>
 * 2 <br>
 */
@Repository
public class CommentDao extends HibernateBaseDao<WorkOpinion> {
	/**
	 * 根据工作id取得意见列表
	 * 建立日期 : June 25, 2013 5:41:50 PM<br>
	 * 作者 : wangamin<br>
	 * @return
	 */
	/*@SuppressWarnings("unchecked")
	public List<WorkOpinion> getWorkOPioionsByWorkId(String workId) {
		String hql = "from WorkOpinion where workId = ?";
		List<WorkOpinion> list = createQuery(hql,workId).list();
		if(list.size()>0){
			return list;
		}
		return null;
	}*/
	/**
	 * 根据工作id 人员id取得意见列表
	 * 建立日期 : June 25, 2013 5:41:50 PM<br>
	 * 作者 : wangamin<br>
	 * @return
	 */
	public WorkOpinion getWorkOPioionsByTodoIdPid(String todoId,
			String personnelId) {
		String hql = "from WorkOpinion where todoId = ? and sigerId=?";
		List<WorkOpinion> list = createQuery(hql,todoId,String.valueOf(personnelId)).list();
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}
	/**
	 * 根据opinionid取得意见列表
	 * 建立日期 : June 25, 2013 5:41:50 PM<br>
	 * 作者 : wangamin<br>
	 * @return
	 */
	public WorkOpinion getWorkOPioionsByOpinionId(String opinionId) {
		String hql = "from WorkOpinion where opinionId = ? ";
		List<WorkOpinion> list = createQuery(hql,opinionId).list();
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}
	
	public List<Object[]> getWrokOptionByWorkIdAndActivity(String workId,String activityName){
		//wangxufeng edit 表关联查询，将签名图片也查找出来
		String sql = "select  w.activity_name, w.opinion_content,w.sign_time, w.siger_name,w.opinion_id,w.siger_id,w.todo_id from wf_work_opinion w where w.work_id = ? and w.activity_name = ?  order by w.sign_time";
		List<Object[]> list = createSQLQuery(sql, new Object[]{workId,activityName}).list();
		if(list.size() > 0){
			return list;
		}
		return null;
	}
	public List<Object[]> getWrokOptionByWorkId(String workId){
		//wangxufeng edit 表关联查询，将签名图片也查找出来
		String sql = "select w.activity_name, w.opinion_content,w.sign_time, w.siger_name, w.opinion_id from wf_work_opinion w where w.work_id = ? order by w.sign_time";
		List<Object[]> list = createSQLQuery(sql, new Object[]{workId}).list();
		if(list.size() > 0){
			return list;
		}
		return null;
	}
	public List<Object[]> getWrokOptionByOptionId(String optionId){
		String sql = "select  activity_name, opinion_content,sign_time, siger_name,opinion_id from wf_work_opinion where opinion_id = ?";
		List<Object[]> list = createSQLQuery(sql, new Object[]{optionId}).list();
		if(list.size() > 0){
			return list;
		}
		return null;
	}
	
	public void delByWorkId(String workId) {
		 
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "delete from WorkOpinion where " + condition + " workId=?";
		this.createQuery(hql, workId).executeUpdate();
	
	}
	
	public List<WorkOpinion> findByWorkId(String workId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from WorkOpinion wo where " + condition + " workId=? order by signTime desc";
		return createQuery(hql, workId).list();
	}
	
	public List<WorkOpinion> findByOpinionName(String opinionName, String appId, String flowId, String workId) {
		//fengjinshou 20160303
		String dialect = null;
		ResourceBundle propFile = ResourceBundle.getBundle("application");
		if(propFile!=null){
			dialect = propFile.getString("hibernate.dialect");
		}
		String comId = AppContext.getCurrentComId();

		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = " and wo.company_id= " + comId;
//		String hql = "from WorkOpinion wo, Control c where opinionName = ? and appId = ? and flowId = ? and c.activityName = wo.activityName" + condition ;
//		String sql = "select wo.* from wf_work_opinion wo, wf_control c where c.opinion_name=? and c.app_id=? and c.flow_id = ? and wo.work_id = ? and c.activity_name = wo.activity_name "+condition + " order by wo.sign_time desc";
//		List<WorkOpinion> list = createQuery(hql, new String[]{opinionName, appId, flowId}).list();
		String sql = null;
		String[] props = null;
		if(StringUtils.isNotBlank(dialect) && dialect.equals("org.hibernate.dialect.Oracle10gDialect")){
			sql = "select t.* from (select wo.*,  ROW_NUMBER() OVER(partition by siger_id order by sign_time desc) as rn from wf_work_opinion wo, wf_control c where c.opinion_name=? and c.app_id=? and c.flow_id = ? and wo.work_id = ? and c.activity_name = wo.activity_name "+condition+")t where t.rn = 1";
			props = new String[]{opinionName, appId, flowId, workId};
		}else{//fengjinshou 20160307
			sql = "select wo.* from wf_work_opinion wo, wf_control c, "+
				  "(select jj.siger_id,substring_index(group_concat(jj.sign_time order by jj.sign_time desc),',',1) sign_time "+
				  "from(select mm.* from wf_work_opinion mm ,wf_control wc where mm.activity_name = wc.activity_name and mm.work_id= ? and wc.opinion_name=? order by mm.sign_time desc) jj group by jj.siger_id) kk "+
				  "where c.opinion_name=? and c.app_id=? and c.flow_id = ? and wo.work_id = ? and c.activity_name = wo.activity_name "+condition+" and wo.siger_id= kk.siger_id and wo.sign_time = kk.sign_time ";
			props = new String[]{workId, opinionName, opinionName, appId, flowId, workId};
		}
		List<WorkOpinion> list = createSQLQuery(sql, props).addEntity(WorkOpinion.class).list();
		return list;
	}
	
	public List<WorkOpinion> findByWorkIdAndTodoId(String workId, String todoId) {
		String comId = AppContext.getCurrentComId();
		String condition = "";
		if (comId != null && !"".equals(comId))
			condition = "companyId='" + comId + "' and ";

		String hql = "from WorkOpinion wo where " + condition + " workId=? and todoId = ?";
		return createQuery(hql, new String[]{workId, todoId}).list();
	}
}
